﻿/******************************************************************
** 文件名:T_004NovelDAL.cs
** Copyright (c) 
** 创建人:
** 日 期:2012-11-14 17:38:24
** 描 述:数据操作类
** 版 本:v1.0
********************************************************************/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Novel.Entity;
using Novel.DAL.Base;

namespace Novel.DAL
{
    /// <summary>
    /// 数据操作类：T_004Novel_DAL
    /// </summary>
    public class T_004Novel_DAL : IT_004NovelManager
    {


        #region 表名
        public  string GetTableName()
        {
            return "T_004Novel";
        }
        #endregion

        #region 插入记录
        /// <summary>
        /// 插入记录
        /// </summary>
        /// <returns></returns>
        public int Insert(T_004NovelEntity model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendFormat("INSERT INTO {0}", GetTableName());
            strSql.Append("(BookNo,BookName,author,intro,Tags,Status,BookPic,BookPath,BookTypeMainID,BookTypeID,TableName,CmfuID,CmfuInfoUrl,CmfuListUrl)");
            strSql.Append(" values ");
            strSql.Append("(@BookNo,@BookName,@author,@intro,@Tags,@Status,@BookPic,@BookPath,@BookTypeMainID,@BookTypeID,@TableName,@CmfuID,@CmfuInfoUrl,@CmfuListUrl)");
            strSql.Append(" SELECT @BookID = @@IDENTITY");
            SqlCommand cmd = new SqlCommand(strSql.ToString());
            cmd.Parameters.Add("@BookID", SqlDbType.Int);
            cmd.Parameters["@BookID"].Direction = ParameterDirection.Output;

            cmd.Parameters.Add("@BookNo", SqlDbType.VarChar, 50);
            cmd.Parameters["@BookNo"].Value = model.BookNo;
            cmd.Parameters.Add("@BookName", SqlDbType.VarChar, 50);
            cmd.Parameters["@BookName"].Value = model.BookName;
            cmd.Parameters.Add("@author", SqlDbType.VarChar, 50);
            cmd.Parameters["@author"].Value = model.author;
            cmd.Parameters.Add("@intro", SqlDbType.VarChar, 500);
            cmd.Parameters["@intro"].Value = model.intro;
            cmd.Parameters.Add("@Tags", SqlDbType.VarChar, 50);
            cmd.Parameters["@Tags"].Value = model.Tags;
            cmd.Parameters.Add("@Status", SqlDbType.TinyInt, 1);
            cmd.Parameters["@Status"].Value = model.Status;
            cmd.Parameters.Add("@BookPic", SqlDbType.NVarChar);
            cmd.Parameters["@BookPic"].Value = model.BookPic;
            cmd.Parameters.Add("@BookPath", SqlDbType.NVarChar);
            cmd.Parameters["@BookPath"].Value = model.BookPath;
            cmd.Parameters.Add("@BookTypeMainID", SqlDbType.Int, 4);
            cmd.Parameters["@BookTypeMainID"].Value = model.BookTypeMainID;
            cmd.Parameters.Add("@BookTypeID", SqlDbType.Int, 4);
            cmd.Parameters["@BookTypeID"].Value = model.BookTypeID;
            cmd.Parameters.Add("@TableName", SqlDbType.NVarChar);
            cmd.Parameters["@TableName"].Value = model.TableName;
            cmd.Parameters.Add("@CmfuID", SqlDbType.Int, 4);
            cmd.Parameters["@CmfuID"].Value = model.CmfuID;
            cmd.Parameters.Add("@CmfuInfoUrl", SqlDbType.NVarChar);
            cmd.Parameters["@CmfuInfoUrl"].Value = model.CmfuInfoUrl;
            cmd.Parameters.Add("@CmfuListUrl", SqlDbType.NVarChar);
            cmd.Parameters["@CmfuListUrl"].Value = model.CmfuListUrl;

            SQLHelper da = new SQLHelper();

            da.BeginTransaction();
            try
            {
                int EffectRows = da.ExecuteNonQuery(cmd);
                model.BookID = (int)cmd.Parameters["@BookID"].Value;
                if (model.BookID > 0)
                {
                    StringBuilder strSql1 = new StringBuilder();
                    strSql1.AppendFormat("INSERT INTO {0}", "T_005NovelInfo");
                    strSql1.Append("(BookID) values (@BookID)");
                   
                    strSql1.Append(" SELECT @InfoID = @@IDENTITY");
                   cmd.CommandText = strSql1.ToString();
                   cmd.Parameters.Clear();
                    cmd.Parameters.Add("@InfoID", SqlDbType.Int);
                    cmd.Parameters["@InfoID"].Direction = ParameterDirection.Output;

                    cmd.Parameters.Add("@BookID", SqlDbType.Int, 4);
                    cmd.Parameters["@BookID"].Value = model.BookID;
                     EffectRows = da.ExecuteNonQuery(cmd);
                   int InfoID = (int)cmd.Parameters["@InfoID"].Value;
                   if (InfoID > 0)
                   {
                       da.Commit();
                   }
                   else
                   {
                       da.Rollback();
                       return 0;
                   }
                }
                return model.BookID;
            }
            catch (Exception)
            {
                da.Rollback();
                return 0;
            }

        }
        #endregion

        #region 更新数据
        /// <summary>
        /// 更新数据
        /// </summary>
        /// <returns></returns>
        public int Update(T_004NovelEntity model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendFormat("UPDATE {0} SET ", GetTableName());
            strSql.Append("BookNo=@BookNo,BookName=@BookName,author=@author,intro=@intro,Tags=@Tags,Status=@Status,BookPic=@BookPic,BookPath=@BookPath,BookTypeMainID=@BookTypeMainID,BookTypeID=@BookTypeID,TableName=@TableName,CmfuID=@CmfuID,CmfuInfoUrl=@CmfuInfoUrl,CmfuListUrl=@CmfuListUrl");
            strSql.Append(" WHERE BookID=@BookID");
            SqlCommand cmd = new SqlCommand(strSql.ToString());
            cmd.Parameters.Add("@BookID", SqlDbType.Int, 4);
            cmd.Parameters["@BookID"].Value = model.BookID;
            cmd.Parameters.Add("@BookNo", SqlDbType.VarChar, 50);
            cmd.Parameters["@BookNo"].Value = model.BookNo;
            cmd.Parameters.Add("@BookName", SqlDbType.VarChar, 50);
            cmd.Parameters["@BookName"].Value = model.BookName;
            cmd.Parameters.Add("@author", SqlDbType.VarChar, 50);
            cmd.Parameters["@author"].Value = model.author;
            cmd.Parameters.Add("@intro", SqlDbType.VarChar, 500);
            cmd.Parameters["@intro"].Value = model.intro;
            cmd.Parameters.Add("@Tags", SqlDbType.VarChar, 50);
            cmd.Parameters["@Tags"].Value = model.Tags;
            cmd.Parameters.Add("@Status", SqlDbType.TinyInt, 1);
            cmd.Parameters["@Status"].Value = model.Status;
            cmd.Parameters.Add("@BookPic", SqlDbType.NVarChar);
            cmd.Parameters["@BookPic"].Value = model.BookPic;
            cmd.Parameters.Add("@BookPath", SqlDbType.NVarChar);
            cmd.Parameters["@BookPath"].Value = model.BookPath;
            cmd.Parameters.Add("@BookTypeMainID", SqlDbType.Int, 4);
            cmd.Parameters["@BookTypeMainID"].Value = model.BookTypeMainID;
            cmd.Parameters.Add("@BookTypeID", SqlDbType.Int, 4);
            cmd.Parameters["@BookTypeID"].Value = model.BookTypeID;
            cmd.Parameters.Add("@TableName", SqlDbType.NVarChar);
            cmd.Parameters["@TableName"].Value = model.TableName;
            cmd.Parameters.Add("@CmfuID", SqlDbType.Int, 4);
            cmd.Parameters["@CmfuID"].Value = model.CmfuID;
            cmd.Parameters.Add("@CmfuInfoUrl", SqlDbType.NVarChar);
            cmd.Parameters["@CmfuInfoUrl"].Value = model.CmfuInfoUrl;
            cmd.Parameters.Add("@CmfuListUrl", SqlDbType.NVarChar);
            cmd.Parameters["@CmfuListUrl"].Value = model.CmfuListUrl;
        
            SQLHelper da = new SQLHelper();
            return da.ExecuteNonQuery(cmd);
        }
        /// <summary>
        /// 添加小说简介
        /// </summary>
        /// <returns></returns>
        public int UpdateIntro(T_004NovelEntity model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendFormat("UPDATE {0} SET ", GetTableName());
            strSql.Append("author=@author,intro=@intro,Tags=@Tags,Status=@Status");
            strSql.Append(" WHERE BookID=@BookID");
            SqlCommand cmd = new SqlCommand(strSql.ToString());
            cmd.Parameters.Add("@BookID", SqlDbType.Int, 4);
            cmd.Parameters["@BookID"].Value = model.BookID;
          
            cmd.Parameters.Add("@author", SqlDbType.VarChar, 50);
            cmd.Parameters["@author"].Value = model.author;
            cmd.Parameters.Add("@intro", SqlDbType.VarChar, 500);
            cmd.Parameters["@intro"].Value = model.intro;
            cmd.Parameters.Add("@Tags", SqlDbType.VarChar, 50);
            cmd.Parameters["@Tags"].Value = model.Tags;
            cmd.Parameters.Add("@Status", SqlDbType.TinyInt, 1);
            cmd.Parameters["@Status"].Value = model.Status;

            SQLHelper da = new SQLHelper();
            return da.ExecuteNonQuery(cmd);
        }
        #endregion

        #region 删除数据
        /// <summary>
        /// 删除数据
        /// </summary>
        /// <returns></returns>
        public int Delete(int id)
        {
            string sql = string.Format("DELETE {0} WHERE BookID=@BookID", GetTableName());
            SqlCommand command = new SqlCommand(sql);
            command.Parameters.Add("@BookID", SqlDbType.Int, 4);
            command.Parameters["@BookID"].Value = id;
            SQLHelper da = new SQLHelper();
            return da.ExecuteNonQuery(command);
        }
        #endregion

        #region IEntityFacade 成员
        /// <summary>
        /// 填充数据
        /// </summary>
        /// <param name="reader"></param>
        public EntityBase FillDetailWithReader(IDataReader reader)
        {
            T_004NovelEntity model = new T_004NovelEntity();
            if (reader["BookID"] != DBNull.Value)
                model.BookID = (int)reader["BookID"];
            if (reader["BookNo"] != DBNull.Value)
                model.BookNo = (string)reader["BookNo"];
            if (reader["BookName"] != DBNull.Value)
                model.BookName = (string)reader["BookName"];
            if (reader["author"] != DBNull.Value)
                model.author = (string)reader["author"];
            if (reader["intro"] != DBNull.Value)
                model.intro = (string)reader["intro"];
            if (reader["Tags"] != DBNull.Value)
                model.Tags = (string)reader["Tags"];
            if (reader["Status"] != DBNull.Value)
                model.Status = (EnumWriteStatus)(byte)reader["Status"];
            if (reader["BookPic"] != DBNull.Value)
                model.BookPic = (string)reader["BookPic"];
            if (reader["BookPath"] != DBNull.Value)
                model.BookPath = (string)reader["BookPath"];
            if (reader["BookTypeMainID"] != DBNull.Value)
                model.BookTypeMainID = (int)reader["BookTypeMainID"];
            if (reader["BookTypeID"] != DBNull.Value)
                model.BookTypeID = (int)reader["BookTypeID"];
            if (reader["TableName"] != DBNull.Value)
                model.TableName = (string)reader["TableName"];
            if (reader["CmfuID"] != DBNull.Value)
                model.CmfuID = (int)reader["CmfuID"];
            if (reader["CmfuInfoUrl"] != DBNull.Value)
                model.CmfuInfoUrl = (string)reader["CmfuInfoUrl"];
            if (reader["CmfuListUrl"] != DBNull.Value)
                model.CmfuListUrl = (string)reader["CmfuListUrl"];
            if (reader["AddTime"] != DBNull.Value)
                model.AddTime = (DateTime)reader["AddTime"];
            return model;
        }

        /// <summary>
        /// 填充数据
        /// </summary>
        /// <param name="reader"></param>
        public EntityBase FillNovelInfo(IDataReader reader)
        {
            NovelEntity model = new NovelEntity();
            if (reader["BookID"] != DBNull.Value)
                model.BookID = (int)reader["BookID"];
            if (reader["BookNo"] != DBNull.Value)
                model.BookNo = (string)reader["BookNo"];
            if (reader["BookName"] != DBNull.Value)
                model.BookName = (string)reader["BookName"];
            if (reader["Status"] != DBNull.Value)
                model.Status = (EnumWriteStatus)(byte)reader["Status"];      
            if (reader["BookPath"] != DBNull.Value)
                model.BookPath = (string)reader["BookPath"];
            if (reader["TableName"] != DBNull.Value)
                model.TableName = (string)reader["TableName"];
            if (reader["CmfuID"] != DBNull.Value)
                model.CmfuID = (int)reader["CmfuID"];
            if (reader["CmfuInfoUrl"] != DBNull.Value)
                model.CmfuInfoUrl = (string)reader["CmfuInfoUrl"];
            if (reader["CmfuListUrl"] != DBNull.Value)
                model.CmfuListUrl = (string)reader["CmfuListUrl"];
            if (reader["LastUpdateTime"] != DBNull.Value)
                model.LastUpdateTime = (DateTime)reader["LastUpdateTime"];
            if (reader["LastChapter"] != DBNull.Value)
                model.LastChapter = (string)reader["LastChapter"];
            if (reader["LastTaskID"] != DBNull.Value)
                model.LastTaskID = (int)reader["LastTaskID"];
            if (reader["LastChapterUrl"] != DBNull.Value)
                model.LastChapterUrl = (string)reader["LastChapterUrl"];
            return model;
        }
        #endregion

        public T_004NovelEntity GetObjectByID(int ID)
        {
            SQLHelper da = new SQLHelper();
            string strSql = string.Format("select * {0} WHERE BookID={0}", GetTableName(),ID);
            SQLHelper.MappEntity<T_004NovelEntity> dele_Entity = delegate(SqlDataReader reader)
            {
                return (T_004NovelEntity)FillDetailWithReader(reader);
            };
            return da.Exceute<T_004NovelEntity>(new SqlCommand(strSql), dele_Entity);

        }
        /// <summary>
        /// 通过起点编号获取小说详情
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public NovelEntity GetNovelByID(int cmfuID)
        {
            SQLHelper da = new SQLHelper();
            string strSql = string.Format("select a.BookID,BookNo,BookName,Status,BookPath,TableName,CmfuID,CmfuInfoUrl,CmfuListUrl,LastUpdateTime,LastChapter,LastTaskID,LastChapterUrl from T_004Novel a,T_005NovelInfo b WHERE a.BookID=b.BookID and CmfuID={0}", cmfuID);

            SQLHelper.MappEntity<NovelEntity> dele_Entity = delegate(SqlDataReader reader)
            {
                return (NovelEntity)FillNovelInfo(reader);
            };
            return da.Exceute<NovelEntity>(new SqlCommand(strSql), dele_Entity);

        }
    
        #region 数据集
        public DataTable ShowTable()
        {
           
            string strSql = string.Format("SELECT  * FROM {0}",GetTableName());
            return SQLHelper.Execute(strSql.ToString());
        }
        #endregion

        #region 分页数据集
        public DataTable ShowTable(PageEntity entity, out int totalPages, out int totalCount)
        {
            QueryBase manager = new QueryBase();

            entity.tbName = GetTableName();
            DataTable dt = manager.GetTable(entity);
            totalPages = entity.TotalPages;
            totalCount = entity.TotalCount;
            return dt;
        }
        #endregion

       
    }
}
